'''
Created on 23 mars 2013

@author: Nils Amiet
'''

# coding: utf-8

'''
Graph 1:
Replies graph
Weighted directed graph
Weight of edge from A to B = number of replies from user A to user B

Graph 2:
User-Discussion graph
Bipartite graph
Edge from User A to discussion D => user A participates in discussion D
'''

import networkx as nx
import networkx.readwrite.gml as gml
import sqlite3
import random
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt

'''Config'''
databasePath = "../../../../../InfrarougeGrabber/infrarouge.db"
graph1Path = "graph1.gml"
graph2Path = "graph2.gml"


userRepliesCounts = {}
graph1 = nx.DiGraph()

userParticipations = []
graph2 = nx.Graph()

def users():
    users = []
    
    with sqlite3.connect(databasePath) as connection:
        cursor = connection.cursor()
        
        query = "SELECT * FROM user"
        cursor.execute(query)
        
        for user in cursor:
            users += [user]
        
        cursor.close()
        
    return users

def getForums():
    forums = []
    
    with sqlite3.connect(databasePath) as connection:
        cursor = connection.cursor()
        
        query = """
        SELECT *
        FROM forum
        """
        
        cursor.execute(query)
        for forum in cursor:
            builtForum = {
                          "id": forum[0],
                          "title": forum[1],
                          "description": forum[2],
                          }
            forums += [builtForum]
            
    return forums

def polarityReplies(forum=None):
    '''Returns a list of replies containing the source, destination, polarity, timestamp and forum id'''
    replies = []
    
    with sqlite3.connect(databasePath) as connection:
        cursor = connection.cursor()
        
        query = """
        SELECT r.fkfrom, r.fkto, m.polarity, m.timestamp, f.id
        FROM reply as r
        INNER JOIN threadmessage as m
            ON r.fkthreadmessage=m.id
        INNER JOIN forumthread as t
            ON m.fkforumthread=t.id
        INNER JOIN forum as f
            ON t.fkparentforum=f.id
        """
        
        if forum is not None:
            query += "WHERE f.id=" + str(forum)
        cursor.execute(query)
        
        for reply in cursor:
            builtReply = {
                          "from": reply[0],
                          "to": reply[1],
                          "polarity": reply[2],
                          "timestamp": reply[3],
                          "forum": reply[4]
                          }
            
            replies  += [builtReply]
        
        cursor.close()
        
    return replies

def countUserReplies():    
    with sqlite3.connect(databasePath) as connection:
        cursor = connection.cursor()
        
        query = "SELECT * FROM reply"
        cursor.execute(query)
        
        for reply in cursor:
            countReply(reply)
        
        cursor.close()
        
def countReply(reply):
    global userRepliesCounts
    
    fromUser = reply[0]
    toUser = reply[1]
#    discussion = reply[2]
    
    fromTo = (fromUser, toUser)
    try:
        userRepliesCounts[fromTo] += 1
    except KeyError:
        userRepliesCounts[fromTo] = 1
        
def createRepliesGraph():
    global graph1
    
    for fromTo, w in userRepliesCounts.items():
        graph1.add_edge(fromTo[0], fromTo[1], weight=w)
    
def buildUserParticipations():
    global userParticipations
    
    with sqlite3.connect(databasePath) as connection:
        cursor = connection.cursor()
        
        query = "SELECT * FROM threadmessage"
        cursor.execute(query)
        
        for threadMessage in cursor:
            forumThread = threadMessage[2]
            user = threadMessage[3]
            userThreadTuple = (user, forumThread)
            userParticipations += [userThreadTuple]
        
        cursor.close()
        
def createParticipationGraph():
    global graph2
    
    users = [x[0] for x in userParticipations]
    discussions = [x[1] for x in userParticipations]

    graph2.add_nodes_from(users, bipartite=0)
    graph2.add_nodes_from(discussions, bipartite=1)
    graph2.add_edges_from(userParticipations)
    
def saveGraphs():
    gml.write_gml(graph1, graph1Path)
    gml.write_gml(graph2, graph2Path)
    
def drawGraph(graph, filename, dpi):
    pos=nx.spring_layout(graph)
#    pos = nx.shell_layout(graph)
#    pos = nx.graphviz_layout(graph)
    nx.draw(graph, pos=pos)
    plt.savefig(filename, dpi=dpi) # save as png

if __name__ == "__main__":
    countUserReplies()
    createRepliesGraph()
    
    buildUserParticipations()
    createParticipationGraph()

    saveGraphs()
    
    print(len(graph1.nodes()))
    print(len(graph2.nodes()))
    
    print(polarityReplies(forum=1))
    print(getForums())
    
    drawGraph(graph1, "g1.png", 180)